﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.DataRowView
Public Class connect
    Dim str As String = "Server=.;Database=QLThueNha;Trusted_Connection=True;"
    Private cn As SqlConnection

    Public Function Connect() As Boolean
        cn = New SqlConnection(str)
        Try
            cn.Open()
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show("Không thể kết nối tới database! " = ex.Message)
        End Try
        Return True
    End Function

    Public Sub Close()
        If cn.State = ConnectionState.Open Then
            cn.Close()
        End If
        cn.Dispose()
    End Sub

    Public Function GetData(ByVal sql As String) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand
        Connect()
        cmd = New SqlCommand(sql, cn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(dt)
        Close()
        Return dt
    End Function
    Public Function GetDataSet(ByVal sql As String) As DataSet
        Dim ds As New DataSet
        Dim cmd As New SqlCommand
        Connect()
        cmd = New SqlCommand(sql, cn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        Close()
        Return ds
    End Function
    Public Function Loadcmb(ByVal sql As String) As SqlDataAdapter
        Connect()
        Dim da As New SqlDataAdapter(sql, cn)
        Close()
        Return da
    End Function
    Public Function LoadLoaiPhong(ByVal sql As String) As DataSet
        Dim ds As New DataSet()
        Connect()
        'cmd = New SqlCommand(sql, cn)
        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(ds, "LOAIPHONG")
        Close()
        Return ds
    End Function
    Public Function LoadQuan(ByVal sql As String) As DataSet
        Dim ds As New DataSet()
        Connect()
        'cmd = New SqlCommand(sql, cn)
        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(ds, "QUAN")
        Close()
        Return ds
    End Function
    Public Function LoadDonVi(ByVal sql As String) As DataSet
        Dim ds As New DataSet()
        Connect()
        'cmd = New SqlCommand(sql, cn)
        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(ds, "DONVI")
        Close()
        Return ds
    End Function
    Public Function LoadLNV(ByVal sql As String) As DataSet
        Dim ds As New DataSet()
        Connect()
        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(ds, "LOAINHANVIEN")
        Close()
        Return ds
    End Function
    Public Sub CreateCommand(ByVal queryString As String, ByVal connectionString As String)
        Using connection As New SqlConnection(connectionString)
            Dim command As New SqlCommand(queryString, connection)
            command.Connection.Open()
            command.ExecuteNonQuery()
            command.Connection.Close()
        End Using
    End Sub

    Public Sub ExecSql(ByVal Sql As String)
        'Dim cn As SqlConnection = New SqlConnection(str)
        'Connect()
        'Dim cmd As SqlCommand = New SqlCommand(Sql, cn)
        'cmd.ExecuteNonQuery()
        CreateCommand(Sql, str)
        'Close()
    End Sub
End Class
